﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using SurveySystem.Model;
using SurveySystem.Utility;

namespace SurveySystem.Repository.MySQL
{
    public class MssqlSurveyRepository : ISurveyRepository
    {
        public Survey GetSurveyByID(int surveyID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            Survey s = null;

            sql = "SELECT `survey_id`, `title`, `description`, `more_info_url`, `owner`, `open_date`, `close_date`, `closed` \n" + //
                  "FROM `survey` WHERE survey_id=@id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", surveyID);

                conn.Open();
                dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    s = new Survey();

                    s.SurveyID = dr.GetInt32(0);
                    s.Title = dr.GetString(1);
                    s.Description = dr.GetString(2);
                    s.MoreInfoUrl = dr.GetString(3);
                    s.Owner = dr.GetString(4);
                    s.OpenDate = dr.GetDateTime(5);
                    s.CloseDate = dr.GetDateTime(6);
                    s.Closed = dr.GetBoolean(7);

                }
                else
                {
                    s = null;
                }

                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return s;
        }

        public List<Survey> GetAllActiveSurveys()
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            List<Survey> list = new List<Survey>();

            sql = "SELECT `survey_id`, `title`, `description`, `more_info_url`, `owner`, `open_date`, `close_date`, `closed` \n" + //
                  "FROM `survey` \n" + //
                  "WHERE `open_date` <= now() AND `close_date` > now() AND `closed`=0";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);

                conn.Open();
                dr = cmd.ExecuteReader();
                
                while (dr.Read())
                {
                    Survey s = new Survey();

                    s.SurveyID = dr.GetInt32(0);
                    s.Title = dr.GetString(1);
                    s.Description = dr.GetString(2);
                    s.MoreInfoUrl = dr.GetString(3);
                    s.Owner = dr.GetString(4);
                    s.OpenDate = dr.GetDateTime(5);
                    s.CloseDate = dr.GetDateTime(6);
                    s.Closed = dr.GetBoolean(7);

                    list.Add(s);
                }

                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<Survey> GetAllSurveys()
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            List<Survey> list = new List<Survey>();

            sql = "SELECT `survey_id`, `title`, `description`, `more_info_url`, `owner`, `open_date`, `close_date`, `closed` \n" + //
                  "FROM `survey`";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Survey s = new Survey();

                    s.SurveyID = dr.GetInt32(0);
                    s.Title = dr.GetString(1);
                    s.Description = dr.GetString(2);
                    s.MoreInfoUrl = dr.GetString(3);
                    s.Owner = dr.GetString(4);
                    s.OpenDate = dr.GetDateTime(5);
                    s.CloseDate = dr.GetDateTime(6);
                    s.Closed = dr.GetBoolean(7);

                    list.Add(s);
                }

                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public void AddSurvey(Survey s)
        {
            throw new NotImplementedException();
        }

        public void UpdateSurvey(Survey s)
        {
            throw new NotImplementedException();
        }

        public void DeleteSurvey(int surveyID)
        {
            throw new NotImplementedException();
        }


    }
}
